/**
 * 
 */
package com.eascs.crm.nsEditDetail.service.impl;

import org.springframework.beans.factory.annotation.Autowired;

import com.alibaba.dubbo.config.annotation.Service;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.StringUtil;
import com.eascs.crm.nsEditDetail.dao.MainStoreInfoDao;
import com.eascs.crm.nsEditDetail.entity.O2oMainStoreInfoEntity;
import com.eascs.crm.nsEditDetail.service.IMainStoreInfoService;

/**@Description:
 *               <p>
 *               实现仓库信息接口
 *               </p>
 * @author LMX
 */
@Service
public class MainStoreInfoServiceImpl implements IMainStoreInfoService{

	@Autowired
	MainStoreInfoDao mainStoreInfoDao;

	/**
	 * @Description:
	 *               <p>
	 *               根据仓库信息id查询实体
	 *               </p>
	 * @author LMX
	 */
	@Override
	public O2oMainStoreInfoEntity getEntityById(String id) {
		return mainStoreInfoDao.getById(O2oMainStoreInfoEntity.class, id);
	}

	/**
	 * @Description:
	 *               <p>
	 *                保存修改
	 *               </p>
	 * @author LMX
	 */
	@Override
	public boolean update(O2oMainStoreInfoEntity info) {
		return mainStoreInfoDao.updateById(info) > 0;
	}

	/**
	 * @Description:
	 *               <p>
	 *                新增
	 *               </p>
	 * @author LMX
	 */
	@Override
	public boolean insert(O2oMainStoreInfoEntity info) {
		return mainStoreInfoDao.insert(info) > 0;
	}

	/**@Description:
	 *               <p>
	 *               根据loanapplyNo查询仓库信息
	 *               </p>
	 * @author LMX
	 */
	@Override
	public O2oMainStoreInfoEntity getByLoanNo(String loanapplyNo) {
		String sql = "SELECT * from o2o_main_store_info t where t.loanapply_no = ?";
		return mainStoreInfoDao.findEntity(sql, O2oMainStoreInfoEntity.class, loanapplyNo);
	}

	/**@Description:
	 *               <p>
	 *               分页查询仓库信息列表
	 *               </p>
	 * @author LMX
	 */
	@Override
	public PageResult<Object[]> page(String loanapplyNo) {
		return this.page(loanapplyNo, 0);
	}
	
	public PageResult<Object[]> page(String loanapplyNo, int pageNo) {
//		String sql = "SELECT t.id, t.store_nature, t.store_type " +
//		" ,CONCAT(DATE_FORMAT(t.rent_start_date,'%Y-%m-%d'),' ~ ',DATE_FORMAT(t.rent_end_date,'%Y-%m-%d')) as rent_StartEnd_date  " +
//	    " ,CONCAT(t.storeprovince,t.storecity,t.storecounty,t.storeaddress) as address " +
//	    " FROM o2o_main_store_info t WHERE t.loanapply_no=? AND t.is_del='N' ORDER BY t.create_date DESC";
		
		String sql = "SELECT t.id, t.store_nature, t.store_type  " +
					" ,CONCAT(DATE_FORMAT(t.rent_start_date,'%Y-%m-%d'),' ~ ',DATE_FORMAT(t.rent_end_date,'%Y-%m-%d')) as rent_StartEnd_date  " +
					" ,CONCAT( " +
						" ifNULL( " +
						" ( " +
							" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
								" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='PROVINCE' " +
								" ) and m.item_code=t.storeprovince " +
						" ),'') ,ifNULL( " +
						" ( " +
							" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
							" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='city' " +
							" ) and m.item_code=t.storecity " +
						" ),'') ,ifNULL( " +
						" ( " +
							" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
							" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='country' " +
							") and m.item_code=t.storecounty " +
						" ),''), ifNULL(t.storeaddress,'')   ) as LongAddress " +
						" FROM o2o_main_store_info t WHERE t.loanapply_no=? AND t.is_del='N' " +
						" ORDER BY t.create_date DESC "; 
		
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, 10, pageNo);
		sqlPageQuery.addParam(loanapplyNo);
		sqlPageQuery.setQuerySql(sql);
		return mainStoreInfoDao.findDataWithPage(sqlPageQuery);
	}
	
	/**@Description:
	 *               <p>
	 *               分页查询仓库信息列表--【脱敏】
	 *               </p>
	 * @author LMX
	 */
	@Override
	public PageResult<Object[]> pageSensitive(String loanapplyNo,String type) {
		return this.pageSensitive(loanapplyNo, 0,type);
	}
	
	public PageResult<Object[]> pageSensitive(String loanapplyNo, int pageNo,String type) {
		
		String sql = "SELECT t.id, t.store_nature, t.store_type  " +
				" ,CONCAT(DATE_FORMAT(t.rent_start_date,'%Y-%m-%d'),' ~ ',DATE_FORMAT(t.rent_end_date,'%Y-%m-%d')) as rent_StartEnd_date  " +
				" ,CONCAT( LEFT ( CONCAT( " +
				" ifNULL( " +
				" ( " +
				" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
				" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='PROVINCE' " +
				" ) and m.item_code=t.storeprovince " +
				" ),'') ,ifNULL( " +
				" ( " +
				" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
				" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='city' " +
				" ) and m.item_code=t.storecity " +
				" ),'') ,ifNULL( " +
				" ( " +
				" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
				" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='country' " +
				") and m.item_code=t.storecounty " +
				" ),''), ifNULL(t.storeaddress,'')   ) , 6), '******') as LongAddress " +
				" FROM o2o_main_store_info t WHERE t.loanapply_no=? AND t.is_del='N' " +
				" ORDER BY t.create_date DESC "; 
		if ("detail".equals(type)) {
			sql = "SELECT t.id, t.store_nature, t.store_type  " +
					" ,CONCAT(DATE_FORMAT(t.rent_start_date,'%Y-%m-%d'),' ~ ',DATE_FORMAT(t.rent_end_date,'%Y-%m-%d')) as rent_StartEnd_date  " +
					" ,CONCAT(   " +
					" ifNULL( " +
					" ( " +
					" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
					" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='PROVINCE' " +
					" ) and m.item_code=t.storeprovince " +
					" ),'') ,ifNULL( " +
					" ( " +
					" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
					" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='city' " +
					" ) and m.item_code=t.storecity " +
					" ),'') ,ifNULL( " +
					" ( " +
					" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
					" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='country' " +
					") and m.item_code=t.storecounty " +
					" ),''), ifNULL(t.storeaddress,'')    ) as LongAddress " +
					" FROM o2o_main_store_info t WHERE t.loanapply_no=? AND t.is_del='N' " +
					" ORDER BY t.create_date DESC "; 
		}
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, 10, pageNo);
		sqlPageQuery.addParam(loanapplyNo);
		sqlPageQuery.setQuerySql(sql);
		return mainStoreInfoDao.findDataWithPage(sqlPageQuery);
	}
	
	/**@Description:
	 *               <p>
	 *               分页查询仓库信息列表--【脱敏】
	 *               </p>
	 * @author LMX
	 */
	@Override
	public PageResult<Object[]> pageSensitiveAS(String loanapplyNo,String type, String approvalStatus) {
		return this.pageSensitiveAS(loanapplyNo, 0, type, approvalStatus);
	}
	
	public PageResult<Object[]> pageSensitiveAS(String loanapplyNo, int pageNo,String type, String approvalStatus) {
		String sql = "SELECT t.id, t.store_nature, t.store_type  " +
				" ,CONCAT(DATE_FORMAT(t.rent_start_date,'%Y-%m-%d'),' ~ ',DATE_FORMAT(t.rent_end_date,'%Y-%m-%d')) as rent_StartEnd_date  " +
				" ,CONCAT( LEFT ( CONCAT( " +
				" ifNULL( " +
				" ( " +
				" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
				" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='PROVINCE' " +
				" ) and m.item_code=t.storeprovince " +
				" ),'') ,ifNULL( " +
				" ( " +
				" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
				" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='city' " +
				" ) and m.item_code=t.storecity " +
				" ),'') ,ifNULL( " +
				" ( " +
				" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
				" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='country' " +
				") and m.item_code=t.storecounty " +
				" ),''), ifNULL(t.storeaddress,'')   ) , 6), '******') as LongAddress " +
				" FROM o2o_main_store_info t WHERE t.loanapply_no=? AND t.is_del='N' " +
				" ORDER BY t.create_date DESC ";
		if(StringUtil.isNotBlank(approvalStatus)){
			if (!"UNDO".equals(approvalStatus)) {
				sql = "SELECT t.id, t.store_nature, t.store_type  " +
						" ,CONCAT(DATE_FORMAT(t.rent_start_date,'%Y-%m-%d'),' ~ ',DATE_FORMAT(t.rent_end_date,'%Y-%m-%d')) as rent_StartEnd_date  " +
						" ,CONCAT( LEFT ( CONCAT( " +
						" ifNULL( " +
						" ( " +
						" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
						" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='PROVINCE' " +
						" ) and m.item_code=t.storeprovince " +
						" ),'') ,ifNULL( " +
						" ( " +
						" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
						" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='city' " +
						" ) and m.item_code=t.storecity " +
						" ),'') ,ifNULL( " +
						" ( " +
						" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
						" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='country' " +
						") and m.item_code=t.storecounty " +
						" ),''), ifNULL(t.storeaddress,'')   ) , 6), '******') as LongAddress " +
						" FROM o2o_main_store_info t WHERE t.loanapply_no=? AND t.is_del='N' " +
						" ORDER BY t.create_date DESC ";
			}else {
				sql = "SELECT t.id, t.store_nature, t.store_type  " +
						" ,CONCAT(DATE_FORMAT(t.rent_start_date,'%Y-%m-%d'),' ~ ',DATE_FORMAT(t.rent_end_date,'%Y-%m-%d')) as rent_StartEnd_date  " +
						" ,CONCAT(   " +
						" ifNULL( " +
						" ( " +
						" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
						" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='PROVINCE' " +
						" ) and m.item_code=t.storeprovince " +
						" ),'') ,ifNULL( " +
						" ( " +
						" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
						" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='city' " +
						" ) and m.item_code=t.storecity " +
						" ),'') ,ifNULL( " +
						" ( " +
						" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
						" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='country' " +
						") and m.item_code=t.storecounty " +
						" ),''), ifNULL(t.storeaddress,'')    ) as LongAddress " +
						" FROM o2o_main_store_info t WHERE t.loanapply_no=? AND t.is_del='N' " +
						" ORDER BY t.create_date DESC "; 
			}
		}else {
			if (!"detail".equals(type)) {
				sql = "SELECT t.id, t.store_nature, t.store_type  " +
						" ,CONCAT(DATE_FORMAT(t.rent_start_date,'%Y-%m-%d'),' ~ ',DATE_FORMAT(t.rent_end_date,'%Y-%m-%d')) as rent_StartEnd_date  " +
						" ,CONCAT( LEFT ( CONCAT( " +
						" ifNULL( " +
						" ( " +
						" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
						" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='PROVINCE' " +
						" ) and m.item_code=t.storeprovince " +
						" ),'') ,ifNULL( " +
						" ( " +
						" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
						" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='city' " +
						" ) and m.item_code=t.storecity " +
						" ),'') ,ifNULL( " +
						" ( " +
						" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
						" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='country' " +
						") and m.item_code=t.storecounty " +
						" ),''), ifNULL(t.storeaddress,'')   ) , 6), '******') as LongAddress " +
						" FROM o2o_main_store_info t WHERE t.loanapply_no=? AND t.is_del='N' " +
						" ORDER BY t.create_date DESC ";
			}else{
				sql = "SELECT t.id, t.store_nature, t.store_type  " +
						" ,CONCAT(DATE_FORMAT(t.rent_start_date,'%Y-%m-%d'),' ~ ',DATE_FORMAT(t.rent_end_date,'%Y-%m-%d')) as rent_StartEnd_date  " +
						" ,CONCAT(   " +
						" ifNULL( " +
						" ( " +
						" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
						" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='PROVINCE' " +
						" ) and m.item_code=t.storeprovince " +
						" ),'') ,ifNULL( " +
						" ( " +
						" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
						" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='city' " +
						" ) and m.item_code=t.storecity " +
						" ),'') ,ifNULL( " +
						" ( " +
						" SELECT m.item_name from  pub_dditem_crm m where m.dd_no=( " +
						" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='country' " +
						") and m.item_code=t.storecounty " +
						" ),''), ifNULL(t.storeaddress,'')    ) as LongAddress " +
						" FROM o2o_main_store_info t WHERE t.loanapply_no=? AND t.is_del='N' " +
						" ORDER BY t.create_date DESC "; 
			}
		}
		
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, 10, pageNo);
		sqlPageQuery.addParam(loanapplyNo);
		sqlPageQuery.setQuerySql(sql);
		return mainStoreInfoDao.findDataWithPage(sqlPageQuery);
	}

	/**@Description:
	 *               <p>
	 *               删除
	 *               </p>
	 * @author LMX
	 */
	@Override
	public boolean delete(String storeId) {
		return mainStoreInfoDao.execute("UPDATE o2o_main_store_info t SET t.is_del='Y' WHERE t.id=?", storeId) > 0;
	}

}
